﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
 

namespace InternalManagementSystem.DAO
{
    public class DBHelper : DataAccessObject
	{
        public static void LogErrorMsg(Guid workflowInstanceId, string msg)
        {
            lock (typeof(DBHelper))
            {
                //to-do
                throw new NotImplementedException();
            }
        }

        public void ExecuteNonQuerySQL(string sql)
        {
            SqlConnection conn = new SqlConnection(ConnectionString);

            // create the command for the stored procedure
            SqlCommand sqlCmd = new SqlCommand();
            sqlCmd.Connection = conn;

            //oracle package body name
            sqlCmd.CommandText = sql;

            try
            {
                // open the connection
                conn.Open();
                sqlCmd.ExecuteNonQuery();
                sqlCmd.Dispose();
                conn.Close();
                conn.Dispose();
            }
            catch (SqlException pEx)
            {
                sqlCmd.Dispose();
                conn.Close();
                conn.Dispose();
                LastErrorMessage = pEx.Message;
                LogErrorMessage();
                throw pEx;
            }
        }

        public DataSet ExecuteQuerySQL(string sql)
        {
            SqlConnection conn = new SqlConnection(ConnectionString);
            DataSet ds = null;
            SqlDataAdapter xAdapter = null;

            // create the command for the stored procedure
            SqlCommand sqlCmd = new SqlCommand();
            sqlCmd.Connection = conn;

            //oracle package body name
            sqlCmd.CommandText = sql;

            try
            {
                // open the connection and create the DataReader
                conn.Open();

                // output the results and add to dataset
                xAdapter = new SqlDataAdapter();
                xAdapter.SelectCommand = sqlCmd;
                ds = new DataSet();
                xAdapter.Fill(ds, "table");
                xAdapter.Dispose();
                sqlCmd.Dispose();
                conn.Close();
                conn.Dispose();
            }
            catch (SqlException pEx)
            {
                sqlCmd.Dispose();
                conn.Close();
                conn.Dispose();
                LastErrorMessage = pEx.Message;
                LogErrorMessage();
                throw pEx;
            }

            return ds;
        }

        public DataSet ExecuteQuerySQL(string sql, List<SqlParameter> paras)
        {
            SqlConnection conn = new SqlConnection(ConnectionString);
            DataSet ds = null;
            SqlDataAdapter xAdapter = null;

            // create the command for the stored procedure
            SqlCommand sqlCmd = new SqlCommand();
            sqlCmd.Connection = conn;

            //oracle package body name
            sqlCmd.CommandText = sql;
            if (null != paras)
            {
                foreach (SqlParameter p in paras)
                {
                    sqlCmd.Parameters.Add(p);
                }
            }

            try
            {
                // open the connection and create the DataReader
                conn.Open();

                // output the results and add to dataset
                xAdapter = new SqlDataAdapter();
                xAdapter.SelectCommand = sqlCmd;
                ds = new DataSet();
                xAdapter.Fill(ds, "table");
                xAdapter.Dispose();
                sqlCmd.Dispose();
                conn.Close();
                conn.Dispose();
            }
            catch (SqlException pEx)
            {
                sqlCmd.Dispose();
                conn.Close();
                conn.Dispose();
                LastErrorMessage = pEx.Message;
                LogErrorMessage();
                throw pEx;
            }

            return ds;
        }
	}
}
